﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_XmissionInspVisitToXmissionAsset')
	DROP PROCEDURE up_XmissionInspVisitToXmissionAsset

GO

CREATE PROCEDURE up_XmissionInspVisitToXmissionAsset
(	
	@vendorProjectId int
)
AS

DECLARE @UtilityId int
DECLARE @UtilityProjectId int

--Gets the Utility ID
SELECT 
	@UtilityId = UtilityFk
FROM 
	tb_vendor_Projects
WHERE  
	Vendor_ProjectId = @vendorProjectId

--Gets the Utility Project Id
SELECT 
	@UtilityProjectId = uProject.Utility_ProjectId
FROM 
	tb_vendor_Projects vProject
INNER JOIN tb_utility_projects  uProject
	ON vProject.VendorFk = uProject.VendorFk
	AND vProject.ProjectTypeFk = uProject.TypeFk
WHERE  
	vProject.Vendor_ProjectId = @vendorProjectId

PRINT 'Insert Assets from inspection visits'

DECLARE @insertedStructureAssetIds TABLE (Utility_Asset_StructureId int, TransmissionPoleId int)

INSERT INTO tb_Utility_Asset_XMissionStructures
(
	UtilityFK
	,Latitude
	,Longitude
	,Altitude
	,SolutionAvailable
	,XMissionStructures_TopTypeFk
	,Utility_ContactFK
	,Date
	,Utility_ProjectFK
	,HaveImages
)
OUTPUT INSERTED.Utility_Asset_XMissionStructureId, INSERTED.Altitude INTO @insertedStructureAssetIds
SELECT 
	@UtilityId
	,Latitude
	,Longitude
	,Vendor_InspVisit_XMissionStructure_PoleId
	,1
	,TopTypeFk
	,inspectorfk
	,GETDATE()
	,@UtilityProjectId
	,0
FROM 
	tb_Vendor_InspVisit_XmissionStructures_Poles
WHERE 
	vendor_projectfk = 19
AND Utility_Asset_XMissionStructures_PoleFk IS NULL

DECLARE @insertedAssetIds TABLE (Utility_Asset_TransmissionId int, TransmissionPoleId int)

INSERT INTO tb_Utility_Asset_XMissionStructures_Poles
(
	Utility_Asset_XMissionStructureFK,
	XMissionStructures_PoleTopTypeFk,
	XMissionStructures_PoleSpeciesFk,
	XMissionStructures_PoleTreatmentFk,
	[Year],
	XMissionStructures_PoleClassFk,
	Latitude,
	Longitude,
	Altitude,
	SatelliteCount,
	SatelliteTime,
	SolutionAvailable,
	AID,
	LVisit,
	Number,
	Length,
	LVisitor,
	MapFk,
	SectionFk
)
OUTPUT INSERTED.Utility_Asset_XMissionStructures_PoleId, INSERTED.Altitude INTO @insertedAssetIds
SELECT 
	[AssetStructures].Utility_Asset_StructureId,
	TopTypeFk,
	SpecieFk,
	TreatmentFk,
	[Year],
	ClassFk,
	Latitude,
	Longitude,
	Vendor_InspVisit_XMissionStructure_PoleId,
	NULL,
	NULL,
	1,
	NULL,
	YEAR(LVisit),
	Number,
	Length,
	LInspector,
	MapFk,
	SectionFk	
FROM 
	tb_Vendor_InspVisit_XmissionStructures_Poles inspPoles
INNER JOIN @insertedStructureAssetIds [AssetStructures]
	ON [AssetStructures].TransmissionPoleId = inspPoles.Vendor_InspVisit_XMissionStructure_PoleId
WHERE 
	vendor_projectfk = 19
AND Utility_Asset_XMissionStructures_PoleFk IS NULL

UPDATE
	tb_Vendor_InspVisit_XmissionStructures_Poles
SET
	Utility_Asset_XMissionStructures_PoleFk = [InsertedIds].Utility_Asset_TransmissionId
FROM 
	@insertedAssetIds [InsertedIds]
WHERE
	tb_Vendor_InspVisit_XmissionStructures_Poles.Vendor_InspVisit_XMissionStructure_PoleId = [InsertedIds].TransmissionPoleId

UPDATE 
	tb_Utility_Asset_XMissionStructures_Poles
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_XMissionStructures_Poles
INNER JOIN @insertedAssetIds [InsertedIds]
	ON tb_Utility_Asset_XMissionStructures_Poles.Utility_Asset_XMissionStructures_PoleId = [InsertedIds].Utility_Asset_TransmissionId

UPDATE 
	tb_Utility_Asset_XMissionStructures
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_XMissionStructures
INNER JOIN @insertedStructureAssetIds [InsertedIds]
	ON [InsertedIds].Utility_Asset_StructureId = tb_Utility_Asset_XMissionStructures.Utility_Asset_XMissionStructureId

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_XMissionInspVisitToAsset_UnitTestInitialize')
	DROP PROCEDURE DevOnly_XMissionInspVisitToAsset_UnitTestInitialize

CREATE proc [dbo].[DevOnly_XMissionInspVisitToAsset_UnitTestInitialize]
(
	@dummyUtilityId int output,
	@dummyVendorId int output
)	
AS

SET NOCOUNT ON	
--Creates dummy utility
INSERT INTO tb_Utilities VALUES ('Dummy Utility', 1, 1)
SELECT @dummyUtilityId = @@IDENTITY

--Creates dummy vendor
INSERT INTO tb_Vendors VALUES ('Dummy Vendor', 2, 1)
SELECT @dummyVendorId = @@IDENTITY

--Creates dummy vendor project
INSERT INTO tb_vendor_projects 
(VendorFk, Name, ProjectTypeFk, UtilityFk, ScheduledStartDate, ActualStartDate, ScheduledEndDate, ScheduledCost, ActualCost, TypeFk)
VALUES (@dummyVendorId, 'Dummy distribution project', 1, @dummyUtilityId, GETDATE(), GETDATE(), GETDATE(), 0, 0, 3)

--Creates dummy users
INSERT INTO tb_vendor_contacts VALUES (@dummyVendorId, 1, 'Dummy name', 'dummy.name', 1, 'password', 1)
INSERT INTO tb_utility_contacts VALUES (@dummyUtilityId, 1, 'Dummy name', 'dummy.name', 1, 'password', 1)

--Gets ids 
DECLARE @vendorContactId int
DECLARE @vendorProjectId int

SELECT 
	@vendorContactId = Vendor_ContactId
FROM 
	tb_vendor_contacts
WHERE
	vendorFk = @dummyVendorId

SELECT 
	@vendorProjectId = Vendor_ProjectID
FROM 
	tb_vendor_projects
WHERE
	vendorFk = @dummyVendorId

--Select 20 tx inspc visit and set as dummy insp visits
DECLARE @insertedInspVisitsStIds TABLE (newVendorInspVisitStId int, Latitude float, Longitude float)

INSERT INTO tb_Vendor_InspVisit_XMissionStructures
(
	Vendor_ProjectFk,
	Vendor_ContactFk,
	SolutionAvailable,
	Latitude,
	Longitude
) OUTPUT INSERTED.Vendor_InspVisit_XMissionStructureId, INSERTED.Latitude, INSERTED.Longitude INTO @insertedInspVisitsStIds
SELECT TOP 20 
	@vendorProjectId,
	@vendorContactId,
	0,
	Latitude,
	Longitude
FROM 
	tb_Vendor_InspVisit_XMissionStructures

INSERT INTO tb_Vendor_InspVisit_XMissionStructures_Poles
(
	Vendor_InspVisit_XMissionStructureFk 
	,Vendor_ProjectFk
	,Vendor_ContactFk
	,Latitude
	,Longitude
	,LVisit
	,AcquisitionDate
)
SELECT
	newVendorInspVisitStId,
	@vendorProjectId,
	@vendorContactId,
	Latitude,
	Longitude,
	GETDATE(),
	GETDATE()
FROM
	@insertedInspVisitsStIds


GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_XMissionInspVisitToAsset_UnitTestCleanUp')
	DROP PROCEDURE DevOnly_XMissionInspVisitToAsset_UnitTestCleanUp

CREATE proc [dbo].[DevOnly_XMissionInspVisitToAsset_UnitTestCleanUp] (
	@dummyUtilityId int,
	@dummyVendorId int
)
AS
/* 	
	Purpose: Clears tables used to test 
*/

SET NOCOUNT ON	

--Delete Tx insp visits
DELETE tb_Vendor_InspVisit_XMissionStructures_Poles
WHERE Vendor_ProjectFk IN (SELECT Vendor_ProjectId FROM tb_Vendor_Projects WHERE VendorFk = @dummyVendorId)

DELETE tb_Vendor_InspVisit_XMissionStructures
WHERE Vendor_ProjectFk IN (SELECT Vendor_ProjectId FROM tb_Vendor_Projects WHERE VendorFk = @dummyVendorId)

DELETE tb_Utility_Asset_XmissionStructures_Poles
WHERE Utility_Asset_XMissionStructureFK IN (SELECT Utility_Asset_XMissionStructureId FROM tb_Utility_Asset_XMissionStructures WHERE UtilityFk = @dummyUtilityId)

DELETE tb_Utility_Asset_XMissionStructures
WHERE UtilityFk = @dummyUtilityId

--Delete users
DELETE tb_vendor_contacts 
WHERE VendorFk = @dummyVendorId

DELETE tb_utility_contacts 
WHERE UtilityFk = @dummyUtilityId

--Delete the project
DELETE tb_vendor_projects
WHERE VendorFk = @dummyVendorId

--Deletes the utility
DELETE tb_Utilities 
WHERE UtilityId = @dummyUtilityId

--Deletes the vendor
DELETE tb_Vendors
WHERE VendorId = @dummyVendorId

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_XMissionInspVisitToAsset_UnitTest')
	DROP PROCEDURE DevOnly_XMissionInspVisitToAsset_UnitTest

CREATE proc [dbo].[DevOnly_XMissionInspVisitToAsset_UnitTest] as
/* 	
	Purpose: Tests stored procedures 
*/
SET NOCOUNT ON	

DECLARE @dummyUtilityId int
DECLARE @dummyVendorId int
DECLARE @x varchar(100)
DECLARE @y varchar(100)

--Initialize data to test
PRINT 'Initializing data to test'
EXEC DevOnly_XMissionInspVisitToAsset_UnitTestInitialize @dummyUtilityId OUTPUT, @dummyVendorId OUTPUT

PRINT 'Dummy utility ID: ' + CONVERT(varchar, @dummyUtilityId)
PRINT 'Dummy vendor ID: ' + CONVERT(varchar, @dummyVendorId)

-- bail on error
if (NOT @@error = 0)
	return(1)

--Executes tests
DECLARE @vendorProjectId int
SELECT @vendorProjectId = Vendor_ProjectId FROM tb_vendor_projects WHERE UtilityFk = @dummyUtilityId

--Gets count of assets that should be created
DECLARE @assetsCount int
DECLARE @inspVisitCount int

SELECT @inspVisitCount = COUNT(*) 
FROM tb_Vendor_InspVisit_XmissionStructures_Poles inspVisits
WHERE inspVisits.Vendor_ProjectFk = @vendorProjectId

--Create assets from visits
EXEC up_XmissionInspVisitToXmissionAsset @vendorProjectId, 20

SELECT @assetsCount = COUNT(*) 
FROM tb_Utility_Asset_XMissionStructures_Poles poles
INNER JOIN tb_Utility_Asset_XMissionStructures st
	ON poles.Utility_Asset_XMissionStructureFK = st.Utility_Asset_XMissionStructureId
WHERE st.UtilityFk = @dummyUtilityId

if (@assetsCount <> @inspVisitCount)
begin
	set @x = 'Assets created: ' + convert(varchar(100), @assetsCount)
	set @y = 'Meters should be converted: ' + convert(varchar(100), @inspVisitCount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'Insp Visits used and assets created should be equal.')
end

SELECT @inspVisitCount = COUNT(*) 
FROM tb_Vendor_InspVisit_XmissionStructures_Poles inspVisits
WHERE inspVisits.Vendor_ProjectFk = @vendorProjectId
AND inspVisits.Utility_Asset_XMissionStructures_PoleFk = NULL

if (@inspVisitCount <> 0)
begin
	set @y = convert(varchar(100), @inspVisitCount)
	set @x = convert(varchar(100), 0)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'Problem in inspection visit asset foreign key, it shouldnt be null')
end

--If I execute it again no assets should be created
--Gets poles converted to compare later
DECLARE @inspVisitsConverted table (idInspVisit int, idAsset int)

INSERT INTO @inspVisitsConverted
(
	idInspVisit,
	idAsset
)
SELECT Vendor_InspVisit_XMissionStructure_PoleId, Utility_Asset_XMissionStructures_PoleFk
FROM tb_Vendor_InspVisit_XmissionStructures_Poles poles
WHERE poles.Vendor_ProjectFK = @vendorProjectId 
AND Utility_Asset_XMissionStructures_PoleFk IS NOT NULL

EXEC up_XmissionInspVisitToXmissionAsset @vendorProjectId, 20

SELECT @inspVisitCount = COUNT(*)
FROM tb_Vendor_InspVisit_XmissionStructures_Poles inspVisits
INNER JOIN @inspVisitsConverted inspVisitsConverted
	ON inspVisits.Vendor_InspVisit_XMissionStructure_PoleId = inspVisitsConverted.idInspVisit
	AND inspVisits.Utility_Asset_XMissionStructures_PoleFk = inspVisitsConverted.idAsset

DECLARE @originalPoles int

SELECT @originalPoles = COUNT(*) FROM @inspVisitsConverted

if (@originalPoles <> @inspVisitCount)
begin
	set @y = convert(varchar(100), @inspVisitCount)
	set @x = convert(varchar(100), @originalPoles)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'New Assets where created. It shouldnt happened')
end

--Deletes data used
PRINT 'Deleting data used to test'
EXEC DevOnly_XMissionInspVisitToAsset_UnitTestCleanUp @dummyUtilityId, @dummyVendorId

-- bail on error
if (NOT @@error = 0)
	return(1)




GO

